package model;

import Bean.Checkout;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.TableModel;

public class HirePayModel extends RootModel
{

    private Checkout Checkout = new Checkout();

    public ResultSetTableModel getAllCustomer()
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select customer.* from customer where customer.ID not in (select reservation.CustomerID from reservation )");
            rstm = new ResultSetTableModel(res, 2);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel filterNameCustomer(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select customer.* From Customer where customer.ID not in (select reservation.CustomerID from reservation ) and (`Full Name` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 2);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel filterPhoneCustomer(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select customer.* From Customer where customer.ID not in (select reservation.CustomerID from reservation ) and (`Phone` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 2);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel filterCMTNDCustomer(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select customer.* From Customer where customer.ID not in (select reservation.CustomerID from reservation ) and (`Identify Card` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 2);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel getAllRoom()
    {

        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("Select room.Name,room.Type,typeroom.Price from room,typeroom where room.state='Còn trống' and room.Type=typeroom.Type");
            rstm = new ResultSetTableModel(res, 6);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel filterNameRoom(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select room.Name,room.Type,typeroom.PriceFrom room,typeroom where room.state='Còn trống' and (typeroom.Type=room.Type) and (`Name` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 6);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel filterTypeRoom(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select room.Name,room.Type,typeroom.Price From room,typeroom where room.state='Còn trống' and (typeroom.Type=room.Type) and (room.`Type` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 6);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel filterStateRoom(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select room.Name,room.Type,typeroom.Price From room,typeroom where room.state='Còn trống' and (typeroom.Type=room.Type) and (room.`State` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 6);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public boolean addHireRoom(int CustomerID, int Room, Date date)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("INSERT INTO reservation(CustomerID,Room,`Date Coming`) VALUES (?,?,?);");
            prst.setInt(1, CustomerID);
            prst.setInt(2, Room);
            long datecoming1 = date.getTime();
            java.sql.Date sql1 = new java.sql.Date(datecoming1);
            prst.setDate(3, sql1);
            if (prst.executeUpdate() > 0)
            {
                return true;
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return false;
    }

    public void changeRoom(int nameroom, String state)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("UPDATE room SET State=? WHERE Name=?");
            prst.setString(1, state);
            prst.setInt(2, nameroom);
            prst.executeUpdate();

        }
        catch (SQLException ex)
        {
            System.out.println("Error HireRoomModel.Changeroom ");
        }
        finally
        {
            ConnectDB.close();
        }
    }

    public ResultSetTableModel getAllCustomerhireRoom()
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select reservation.ID,customer.`Full Name`,Birthday,`Identify Card`,gender, Phone,Addrdess,`Date Coming`,Room, room.type,typeroom.Price from customer,room,reservation,typeroom where (customer.ID=reservation.CustomerID) and (room.name=reservation.Room) and (room.type=typeroom.type)");
            rstm = new ResultSetTableModel(res, 3);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel getAllRoomCategory()
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("Select `Date Leaving`,`Date Coming`,`Full Name`,Birthday,`Identify Card`,gender,Phone,Addrdess,Room, type,Sumcost From CustomerHistory ");
            rstm = new ResultSetTableModel(res, 4);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public Checkout getInforCustomer(int ID)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("select customer.`Full Name`,Birthday,`Identify Card`,gender,Phone,Addrdess,`Date Coming`,Room, room.type,typeroom.Price from customer,room,reservation,typeroom where (customer.ID=reservation.CustomerID) and (room.name=reservation.Room) and (room.type=typeroom.type) and (reservation.ID=?)");
            prst.setInt(1, ID);
            ResultSet rs = prst.executeQuery();
            while (rs.next())
            {
                Checkout.setNamecustomer(rs.getString("Full Name"));
                Checkout.setGenderCustomer(rs.getString("Gender"));
                Checkout.setBirtday(rs.getInt("Birthday"));
                Checkout.setPhone(rs.getLong("Phone"));
                Checkout.setAddress(rs.getString("Addrdess"));
                Checkout.setCMTND(rs.getString("Identify Card"));
                Checkout.setDatecoming(rs.getDate("Date Coming"));
                Checkout.setNameroom(rs.getInt("Room"));
                Checkout.setTyperoom(rs.getString("room.type"));
                Checkout.setCostroom(rs.getString("Price"));
            }

        }
        catch (SQLException ex)
        {
            Logger.getLogger(HirePayModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
        return Checkout;
    }

    public void deleteCustomer(int nameroom)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("DELETE FROM reservation WHERE room=? ");
            prst.setInt(1, nameroom);
            prst.executeUpdate();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }
    }

    public void addCustomerHistory(Checkout Checkout)
    {
        ConnectDB.connect();
        try
        {
            prst = ConnectDB.getPrst("INSERT INTO CustomerHistory(`Full Name`,Birthday,Gender,`Identify Card`,Addrdess,Phone,Room,Type,`Date Coming`,`Date Leaving`,SumCost) VALUES (?,?,?,?,?,?,?,?,?,?,?);");
            prst.setString(1, Checkout.getNamecustomer());
            prst.setInt(2, Checkout.getBirtday());
            prst.setString(3, Checkout.getGenderCustomer());
            prst.setString(4, Checkout.getCMTND());
            prst.setString(5, Checkout.getAddress());
            prst.setLong(6, Checkout.getPhone());
            prst.setInt(7, Checkout.getNameroom());
            prst.setString(8, Checkout.getTyperoom());
            long datecoming1 = Checkout.getDatecoming().getTime();
            java.sql.Date sql1 = new java.sql.Date(datecoming1);
            prst.setDate(9, sql1);
            long dateleaving1 = Checkout.getDateleaving().getTime();
            java.sql.Date sql2 = new java.sql.Date(dateleaving1);
            prst.setDate(10, sql2);
            prst.setString(11, Checkout.getSumcost());
            prst.executeUpdate();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(RoomManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            ConnectDB.close();
        }


    }

    public ResultSetTableModel filterCustomer(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select reservation.ID,customer.`Full Name`,Birthday,`Identify Card`,Gender,Phone,Addrdess,`Date Coming`,Room, room.type,typeroom.Price from customer,room,reservation,typeroom where (customer.ID=reservation.CustomerID) and (room.name=reservation.Room) and (room.type=typeroom.type)and( customer.`Full Name` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 3);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public ResultSetTableModel filterRoom(String key)
    {
        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("select reservation.ID,customer.`Full Name`,Birthday,`Identify Card`,Gender,Phone,Addrdess,`Date Coming`,Room, room.type,typeroom.Price from customer,room,reservation,typeroom where (customer.ID=reservation.CustomerID) and (room.name=reservation.Room) and (room.type=typeroom.type)and( reservation.`Room` like '%" + key + "%')");
            rstm = new ResultSetTableModel(res, 3);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }

    public TableModel getAll(Date tu, Date den)
    {
        long tu1 = tu.getTime();
        java.sql.Date sql1 = new java.sql.Date(tu1);
        long den1 = den.getTime();
        java.sql.Date sql2 = new java.sql.Date(den1);

        try
        {
            ConnectDB.connect();
            state = ConnectDB.getState();
            res = state.executeQuery("Select `Date Leaving`,`Date Coming`,`Full Name`,Birthday,`Identify Card`,Gender,Phone,Addrdess,Room, type,Sumcost From CustomerHistory where (`Date Leaving` between'" + sql1 + "'and'" + sql2 + "')");
            rstm = new ResultSetTableModel(res, 4);
            ConnectDB.close();

        }
        catch (SQLException ex)
        {
            Logger.getLogger(CustomerManagerModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return rstm;
        }
    }
}
